import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: seth.yang
 * Date: 2010-11-2
 * Time: 11:40:52
 */
public class Trans {
    static Map<String, Map<String, String>> mapping = new HashMap<String, Map<String, String>> ();
    static Map<String, String> tables = new HashMap<String, String> ();

    static {
        tables.put ("运货商", "shipper");
        tables.put ("类别", "category");
        tables.put ("客户", "customer");
        tables.put ("雇员", "employee");
        tables.put ("供应商", "supplier");
        tables.put ("订单明细", "order_detail");
        tables.put ("订单", "orders");
        tables.put ("产品", "product");
    }


    static Connection getAccessConn () throws SQLException, ClassNotFoundException {
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=d:\\tmp\\Northwind.mdb";
        Class.forName (driver);
        return DriverManager.getConnection (url, "", "");
    }

    static Connection getPqsqlConn () throws ClassNotFoundException, SQLException {
        Class.forName ("org.postgresql.Driver");
        String url = "jdbc:postgresql://192.168.19.2/northwind";
        return DriverManager.getConnection (url, "sample", "sample");
    }

    public static void main (String[] args) throws Exception {
        Connection access = getAccessConn ();
        Connection pgsql = getPqsqlConn ();

        try {
            for (String name : tables.keySet ()) {
                transData (access, pgsql, name);
            }
        } catch (SQLException ex) {
            System.out.println ("code: " + ex.getErrorCode ());
            System.out.println ("state: " + ex.getSQLState ());
            throw ex;
        } finally {
            access.close ();
            pgsql.close ();
        }
    }

    static void transData (Connection access, Connection pgsql, String tableName) throws SQLException {
        String aSql = " SELECT * FROM [" + tableName + "]";
        String pSql = "select * from " + tables.get (tableName);
        String trancate = "truncate table " + tables.get (tableName);
        PreparedStatement as = access.prepareStatement (aSql);
        ResultSet ars = as.executeQuery ();
        Statement ps = pgsql.createStatement ();
        ps.execute (trancate);
        ResultSet prs = ps.executeQuery (pSql);
        ResultSetMetaData arsmd = ars.getMetaData ();
        ResultSetMetaData prsmd = prs.getMetaData ();
        int count = arsmd.getColumnCount ();
        StringBuilder builder = new StringBuilder ("insert into " + tables.get (tableName) + "(");
        StringBuilder values = new StringBuilder ("(");
        for (int i = 1; i <= count; i ++) {
            String name = prsmd.getColumnName (i);
            if (i != 1) {
                builder.append (", ");
                values.append (", ");
            }
            builder.append (name);
            values.append ('?');
        }
        builder.append (") values ").append (values).append (')');
        System.out.println (builder);

        PreparedStatement pstmt = pgsql.prepareStatement (builder.toString ());

        while (ars.next ()) {
            for (int i = 1; i <= count; i ++) {
                int type = prsmd.getColumnType (i);
                Object o = ars.getObject (i);
                pstmt.setObject (i, o, type);
            }
            pstmt.addBatch ();
        }
        pstmt.executeBatch ();
    }
}